
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.LinkedList;

/**
 * This class handles operations on the database. It can store measures sent by clients, get
 * orders submitted by network administrators and remove those.<p>
 *
 * This class is a thread because measures to store are temporarly saved in a list. If the
 * database server crashes, those measures are kept in memory untill the server is operationnal
 * again.
 * 
 * @author Alexandre Dos Santos Coelho
 */
public class DataManager extends Thread {

  private Server server; // Attached server
  private LinkedList<Measure> measures = new LinkedList<Measure>(); // Measures to store

  /**
   * Constructor. Creates and starts the datamanager.
   * @param s Attached Server
   */
  public DataManager(Server s) {
    server = s;
    start();

    // This tread groups and compacts measures for the last day
    new Thread() {

      public void run() {
        while (true) {
          try {
            Connection dbConnection = server.getDbConnection();
            Statement statement = (Statement) dbConnection.createStatement();

            // Generating today and yesterday date
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String today = sdf.format(cal.getTime());
            cal.add(Calendar.DATE, -1);
            String yesterday = sdf.format(cal.getTime());


            // Grouping electric consumption for current day
            ResultSet rs = (ResultSet) statement.executeQuery("SELECT id, type, username, start, SUM(duration), SUM(consumption) FROM (SELECT * FROM measures WHERE compact='0' AND START BETWEEN '" + yesterday + " 00:00:00' AND '" + today + " 00:00:00' ORDER BY start ASC) AS m  GROUP BY id, type, username");
            LinkedList<String> queries = new LinkedList<String>();

            // Generating queries
            while (rs.next()) {
              queries.add("INSERT INTO measures (id, type, username, start, duration, consumption, compact) VALUES ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3) + "','" + rs.getString(4) + "'," + rs.getInt(5) + "," + rs.getFloat(6) + ",1)");
            }

            // Executing queries
            for (String q : queries) {
              statement.executeUpdate(q);
            }

            // Deleting non compacted measures
            statement.executeUpdate("DELETE FROM measures WHERE compact='0' AND START BETWEEN '" + yesterday + " 00:00:00' AND '" + today + " 00:00:00'");

          } catch (Exception e) {
            System.err.println(Logger.getTime() + "Impossible to store compacted measures");
          }
          try {
            Thread.sleep(86400000); // Executing every 24 hours
          } catch (InterruptedException ex) {
          }
        }
      }
    }.start();

    // This tread groups and compacts measures for the last 15 days
    new Thread() {

      public void run() {
        while (true) {
          try {
            Connection dbConnection = server.getDbConnection();
            Statement statement = (Statement) dbConnection.createStatement();

            // Generating today and yesterday date
            Calendar cal = Calendar.getInstance();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String today = sdf.format(cal.getTime());
            cal.add(Calendar.DATE, -15);
            String twoWeeks = sdf.format(cal.getTime());


            // Grouping electric consumption for current day
            ResultSet rs = (ResultSet) statement.executeQuery("SELECT id, type, username, start, SUM(duration), SUM(consumption) FROM (SELECT * FROM measures WHERE compact='1' AND START BETWEEN '" + twoWeeks + " 00:00:00' AND '" + today + " 00:00:00' ORDER BY start ASC) AS m  GROUP BY id, type, username");
            LinkedList<String> queries = new LinkedList<String>();

            // Generating queries
            while (rs.next()) {
              queries.add("INSERT INTO measures (id, type, username, start, duration, consumption, compact) VALUES ('" + rs.getString(1) + "','" + rs.getString(2) + "','" + rs.getString(3) + "','" + rs.getString(4) + "'," + rs.getInt(5) + "," + rs.getFloat(6) + ",2)");
            }

            // Executing queries
            for (String q : queries) {
              statement.executeUpdate(q);
            }

            // Deleting non compacted measures
            statement.executeUpdate("DELETE FROM measures WHERE compact='1' AND START BETWEEN '" + twoWeeks + " 00:00:00' AND '" + today + " 00:00:00'");

          } catch (Exception e) {
            System.err.println(Logger.getTime() + "Impossible to store compacted measures");
          }
          try {
            Thread.sleep(1339200000); // Executing every 15 days
          } catch (InterruptedException ex) {
          }
        }
      }
    }.start();
  }

  /**
   * Adds a measure in queue to be stored. This queue will be stored only when an operationnal
   * link to the database exists.
   * @param m
   */
  public synchronized void storeMeasure(Measure m) {
    if (m != null) {
      measures.add(m);
    }
  }

  /**
   * Retrieves orders from the database and returns them in a ResultSet
   * @return orders present in the database
   */
  public ResultSet getOrders() {
    ResultSet rs = null;
    try {
      Statement statement = (Statement) server.getDbConnection().createStatement();
      // Selecting orders from database
      rs = (ResultSet) statement.executeQuery("SELECT * FROM orders ORDER BY id DESC");
      return rs;
    } catch (SQLException ex) {
      server.setDatabaseConnected(false);
      server.reconnectDB();
    }

    return rs;
  }

  /**
   * Removes an order (if existing) from the database
   * @param o
   */
  public synchronized void removeOrder(Order o) {
    try {
      Statement statement = (Statement) server.getDbConnection().createStatement();
      statement.executeUpdate("DELETE FROM orders WHERE id='" + o.getId() + "'");
      System.out.println(Logger.getTime() + "Deleted order: " + o.getId() + " from database");
    } catch (SQLException ex) {
      System.err.println(Logger.getTime() + "Could not delete order: " + o);
    }

  }

  /**
   * Thread run method. Stores measures in the database when an operationnal connection has been
   * established.
   */
  public void run() {
    while (true) {
      while (server.isDatabaseConnected()) { // Only when the database is connected
        LinkedList<Measure> toDelete = new LinkedList<Measure>();
        synchronized (this) {
          for (Measure m : measures) {
            try {
              // Creating SQL statement
              Statement statement = (Statement) server.getDbConnection().createStatement();

              // Inserting measure into database
              statement.executeUpdate("INSERT INTO measures (id, type, start, duration, consumption, username) " +
                      "VALUES ('" + m.getMachineId() + "', '" + m.getMachineType() + "', '" + m.getStart() + "', '" + m.getDuration() + "', " +
                      m.getConsumption() + ", '" + m.getUserName() + "')");
              toDelete.add(m);
            } catch (SQLException ex) {
              server.setDatabaseConnected(false);
              server.reconnectDB();
              System.err.println(Logger.getTime() + "Database connection error");
            }
          }

          // Deleting added measures from queue
          for (Measure m : toDelete) {
            System.out.println(Logger.getTime() + "Stored measure: " + m.toString() + " in database");
            measures.remove(m);
          }
        }
        try {
          Thread.sleep(2000);
        } catch (InterruptedException ex) {
        }
      }
      try {
        Thread.sleep(2000);
      } catch (InterruptedException ex) {
      }
    }
  }
}
